package com.hl.card.modular.packBack.controller;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.hl.card.common.persistence.model.Goods;
import com.hl.card.common.util.DateUtils;
import com.hl.card.common.util.ResponseData;
import com.hl.card.modular.packBack.service.StatisticsManagerService;

@Controller
@RequestMapping("statisticsManager")
public class StatisticsManagerContoller{
     
	@Autowired
	private StatisticsManagerService statisticsManagerService;
	
    private String PREFIX = "/packback/reportForm/";
	
	/**
	 * 空投比豆页面
	 * @return
	 */
    @RequestMapping("/bibean")
    public String indexBibean(){
        return PREFIX+"airborne.html";
    }
    
	/**
	 * 运营活动页面
	 * @return
	 */
    @RequestMapping("/operate")
    public String indexOperate(){
        return PREFIX+"activity.html";
    }
    
	/**
	 * 社区管理员统计页面
	 * @return
	 */
    @RequestMapping("/communityUser")
    public String communityUser(){
        return PREFIX+"communityUserView.html";
    }
    
	/**
	 * 导出空投比豆excel报表
	 * 
	 * @param request
	 * @param response
	 * @param startTime
	 * @param endTime
	 * @throws IOException
	 */
	@RequestMapping("/downloadBiBeanReport")
	@ResponseBody
	public ResponseData downloadBiBeanReport(HttpServletRequest request,String start_time,String end_time,String interval,
			HttpServletResponse response) throws IOException {
		ResponseData responseData=new ResponseData();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time)&&StringUtils.isNotEmpty(interval))
		{
			responseData.setData(null);
			responseData.setMsg("查询天数和日期条件只支持一种模式！");
			responseData.setStatus("0");
			return responseData;
		}
		if((StringUtils.isNotEmpty(start_time)&&StringUtils.isEmpty(end_time))||(StringUtils.isEmpty(start_time)&&StringUtils.isNotEmpty(end_time)))
		{
			responseData.setData(null);
			responseData.setMsg("查询日期条件错误！");
			responseData.setStatus("0");
			return responseData;
		}
		
		Map<String,Object> map=new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time))
		{
			SimpleDateFormat formate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date startDay = formate.parse(start_time+" 00:00:00");
				Date endDay = formate.parse(end_time+" 00:00:00");
				
				Date now=formate.parse(DateUtils.getNow("yyyy-MM-dd HH:mm:ss"));
				int t_flag = DateUtils.getDateDaysBetween(endDay, now);
				if(t_flag<0)
				{
					responseData.setMsg("结束时间不能大于当前时间");
					responseData.setStatus("0");
					return responseData;
				}
				int days = DateUtils.getDateDaysBetween(startDay, endDay);
				if (days < 0) {
					responseData.setMsg("开始时间不能大于结束时间");
					responseData.setStatus("0");
					return responseData;
				}
				if (days > 90) {
					responseData.setMsg("查询订单的时间跨度不能大于三个月");
					responseData.setStatus("0");
					return responseData;
				}
				
			} catch (ParseException e) {
				e.printStackTrace();
			}
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		if(StringUtils.isNotEmpty(interval))
		{
			if(Integer.parseInt(interval)>90)
			{
				responseData.setMsg("查询订单的时间跨度不能大于三个月");
				responseData.setStatus("0");
				return responseData;
			}
			map.put("interval", interval);
		}
		if(StringUtils.isEmpty(start_time)&&StringUtils.isEmpty(end_time)&&StringUtils.isEmpty(interval))
		{
			map.put("interval", 7);
		}

		List<Map<String,Object>> list=statisticsManagerService.searchStatistics(map);
		
		List<Map<String,Object>> o_list=statisticsManagerService.searchOperateCount(map);
		
		List<Map<String,Object>> a_list=statisticsManagerService.searchDisMemberCount(map);
		
		List<Map<String,Object>> result_list=new ArrayList<Map<String,Object>>();
		
		for(int i=0;i<list.size();i++)
		{
		    Map<String,Object> m=list.get(i);
		    m.putAll(o_list.get(i));
		    m.putAll(a_list.get(i));
		    result_list.add(m);
		}
		//声明一个头部信息
		List<String> headers = Arrays.asList("统计日期", "卡包新增用户数", "注册发放比豆数", "注册用户数(普通注册)", "注册发放比豆数(普通注册)", 
				"注册用户数(邀请注册)", "注册发放比豆数(邀请注册)", "邀请注册次数", "邀请注册发放比豆数", "每日签到用户数","签到发放比豆数","vip奖励领取用户数",
				"vip奖励发放比豆数","成为合伙人用户数","合伙人累计购买矿机数量","合伙人累计发放比豆数");
		
		
		//声明一个报表字段信息
		List<String> values = Arrays.asList("show_time", "add_user_count", "bibean_count", "putong_user_count", "putong_bibean_count", 
				"yaoqing_user_count", "yaoqing_bibean_count", "yaoqing_count", "yaoqing_bibean_count2", "day_user_count","day_bibean_count","vip_user_count",
				"vip_bibean_count","dis_member_user_count","rob_count","dismember_bibean_count");

		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultColumnWidth((short) 24);
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderBottom(BorderStyle.THIN); //下边框    
		style.setBorderLeft(BorderStyle.THIN);//左边框    
		style.setBorderTop(BorderStyle.THIN);//上边框    
		style.setBorderRight(BorderStyle.THIN);//右边框  
		style.setAlignment(HorizontalAlignment.CENTER); // 居中
		style.setFillForegroundColor((short) 13);// 设置背景色
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(BorderStyle.THIN); //下边框    
		style2.setBorderLeft(BorderStyle.THIN);//左边框    
		style2.setBorderTop(BorderStyle.THIN);//上边框    
		style2.setBorderRight(BorderStyle.THIN);//右边框  
		style2.setAlignment(HorizontalAlignment.CENTER); // 居中
		HSSFRow row = sheet.createRow(0);
		
		for(String head:headers){
		HSSFCell cell = row.createCell(headers.indexOf(head));
		HSSFRichTextString richString = new HSSFRichTextString(head);
		HSSFFont font3 = workbook.createFont();
		font3.setBold(true);
		richString.applyFont(font3);
    	cell.setCellValue(richString);
        cell.setCellStyle(style);
		
		}
		for(int i=0;i<result_list.size();i++){
		 row = sheet.createRow(i+1);
		 Map<String,Object> map_result=result_list.get(i);
		for(int j=0;j<values.size();j++)
		{
			 HSSFCell cell = row.createCell(j);
			 String textValue = map_result.get(values.get(j)).toString();
			 HSSFRichTextString richString = new HSSFRichTextString(textValue);
			 cell.setCellValue(richString);
			 cell.setCellStyle(style2);
		}
		}

		response.setContentType("application/octet-stream");
		response.setHeader("Content-disposition", "attachment;filename="
				+ new String(("空投比豆.xls").getBytes("UTF-8"), "ISO8859-1"));// 默认Excel名称
		response.flushBuffer();
		workbook.write(response.getOutputStream());
		responseData.setData(null);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	
	/**
	 * 导出运营活动excel报表
	 * 
	 * @param request
	 * @param response
	 * @param startTime
	 * @param endTime
	 * @throws IOException
	 */
	@RequestMapping("/downloadOperateReport")
	public ResponseData downloadReport(String start_time,String end_time,String interval,String type_code,String type_code_child,HttpServletRequest request,
			HttpServletResponse response) throws IOException {
		ResponseData responseData=new ResponseData();
		if(StringUtils.isEmpty(type_code))
		{
			responseData.setData(null);
			responseData.setMsg("类型不能为空！");
			responseData.setStatus("0");
			return responseData;
		}
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time)&&StringUtils.isNotEmpty(interval))
		{
			responseData.setData(null);
			responseData.setMsg("查询天数和日期条件只支持一种模式！");
			responseData.setStatus("0");
			return responseData;
		}
		if((StringUtils.isNotEmpty(start_time)&&StringUtils.isEmpty(end_time))||(StringUtils.isEmpty(start_time)&&StringUtils.isNotEmpty(end_time)))
		{
			responseData.setData(null);
			responseData.setMsg("查询日期条件错误！");
			responseData.setStatus("0");
			return responseData;
		}
		
		Map<String,Object> map=new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time))
		{
			SimpleDateFormat formate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date startDay = formate.parse(start_time+" 00:00:00");
				Date endDay = formate.parse(end_time+" 00:00:00");
				
				Date now=formate.parse(DateUtils.getNow("yyyy-MM-dd HH:mm:ss"));
				int t_flag = DateUtils.getDateDaysBetween(endDay, now);
				if(t_flag<0)
				{
					responseData.setMsg("结束时间不能大于当前时间");
					responseData.setStatus("0");
					return responseData;
				}
				int days = DateUtils.getDateDaysBetween(startDay, endDay);
				if (days < 0) {
					responseData.setMsg("开始时间不能大于结束时间");
					responseData.setStatus("0");
					return responseData;
				}
				if (days > 90) {
					responseData.setMsg("查询订单的时间跨度不能大于三个月");
					responseData.setStatus("0");
					return responseData;
				}
				
			} catch (ParseException e) {
				e.printStackTrace();
			}
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		if(StringUtils.isNotEmpty(interval))
		{
			if(Integer.parseInt(interval)>90)
			{
				responseData.setMsg("查询订单的时间跨度不能大于三个月");
				responseData.setStatus("0");
				return responseData;
			}
			map.put("interval", interval);
		}
		if(StringUtils.isEmpty(start_time)&&StringUtils.isEmpty(end_time)&&StringUtils.isEmpty(interval))
		{
			map.put("interval", 7);
		}
	    map.put("type_code", type_code);
	    map.put("type_code_child", type_code_child);
		List<Map<String,Object>> result_list=statisticsManagerService.searchOperateInfo(map);
		//声明一个头部信息
		List<String> headers = Arrays.asList("统计日期", "累计参与用户数", "累计发放比豆数");
		//声明一个报表字段信息
		List<String> values = Arrays.asList("show_time","user_count","bibean_count");
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultColumnWidth((short) 24);
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderBottom(BorderStyle.THIN); //下边框    
		style.setBorderLeft(BorderStyle.THIN);//左边框    
		style.setBorderTop(BorderStyle.THIN);//上边框    
		style.setBorderRight(BorderStyle.THIN);//右边框  
		style.setAlignment(HorizontalAlignment.CENTER); // 居中
		style.setFillForegroundColor((short) 13);// 设置背景色
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(BorderStyle.THIN); //下边框    
		style2.setBorderLeft(BorderStyle.THIN);//左边框    
		style2.setBorderTop(BorderStyle.THIN);//上边框    
		style2.setBorderRight(BorderStyle.THIN);//右边框  
		style2.setAlignment(HorizontalAlignment.CENTER); // 居中
		HSSFRow row = sheet.createRow(0);
		
		for(String head:headers){
		HSSFCell cell = row.createCell(headers.indexOf(head));
		HSSFRichTextString richString = new HSSFRichTextString(head);
		HSSFFont font3 = workbook.createFont();
		font3.setBold(true);
		richString.applyFont(font3);
    	cell.setCellValue(richString);
        cell.setCellStyle(style);
		
	
		}
		for(int i=0;i<result_list.size();i++){
		 row = sheet.createRow(i+1);
		 Map<String,Object> map_result=result_list.get(i);
		for(int j=0;j<values.size();j++)
		{
			 HSSFCell cell = row.createCell(j);
			 String textValue = map_result.get(values.get(j)).toString();
			 HSSFRichTextString richString = new HSSFRichTextString(textValue);
			 cell.setCellValue(richString);
			 cell.setCellStyle(style2);
		}
		}
		
		response.setContentType("application/octet-stream");
		response.setHeader("Content-disposition", "attachment;filename="
				+ new String(("运营活动.xls").getBytes("UTF-8"), "ISO8859-1"));// 默认Excel名称
		response.flushBuffer();
		workbook.write(response.getOutputStream());
		responseData.setData(null);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	
	/**
	 * 查询大叔社区答卷类型
	 * @return
	 */
	@RequestMapping("/selectGoodsByCodePrefix")
	@ResponseBody
	public ResponseData selectGoodsByCodePrefix()
	{
	    List<Goods> list=statisticsManagerService.selectGoodsByCodePrefix();
	    ResponseData responseData=new ResponseData();
		responseData.setData(list);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	/**
	 * 空投比豆统计报表
	 * @param start_time 开始日期
	 * @param end_time 结束日期
	 * @param interval 天数
	 * @return
	 */
	@RequestMapping("/searchStatistics")
	@ResponseBody
	public ResponseData  searchStatistics(String start_time,String end_time,String interval,Integer page,Integer size)
	{
		int total_count=7;
		ResponseData responseData=new ResponseData();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time)&&StringUtils.isNotEmpty(interval))
		{
			responseData.setData(null);
			responseData.setMsg("查询天数和日期条件只支持一种模式！");
			responseData.setStatus("0");
			return responseData;
		}
		if((StringUtils.isNotEmpty(start_time)&&StringUtils.isEmpty(end_time))||(StringUtils.isEmpty(start_time)&&StringUtils.isNotEmpty(end_time)))
		{
			responseData.setData(null);
			responseData.setMsg("查询日期条件错误！");
			responseData.setStatus("0");
			return responseData;
		}
		
		Map<String,Object> map=new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time))
		{
			SimpleDateFormat formate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date startDay = formate.parse(start_time+" 00:00:00");
				Date endDay = formate.parse(end_time+" 00:00:00");
				Date now=formate.parse(DateUtils.getNow("yyyy-MM-dd HH:mm:ss"));
				int t_flag = DateUtils.getDateDaysBetween(endDay, now);
				if(t_flag<0)
				{
					responseData.setMsg("结束时间不能大于当前时间");
					responseData.setStatus("0");
					return responseData;
				}
				int days = DateUtils.getDateDaysBetween(startDay, endDay);
				if (days < 0) {
					responseData.setMsg("开始时间不能大于结束时间");
					responseData.setStatus("0");
					return responseData;
				}
				if (days > 90) {
					responseData.setMsg("查询订单的时间跨度不能大于三个月");
					responseData.setStatus("0");
					return responseData;
				}
				total_count=days+1;
				
			} catch (ParseException e) {
				e.printStackTrace();
			}
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		if(StringUtils.isNotEmpty(interval))
		{
			if(Integer.parseInt(interval)>90)
			{
				responseData.setMsg("查询订单的时间跨度不能大于三个月");
				responseData.setStatus("0");
				return responseData;
			}
			map.put("interval", interval);
		}
		if(StringUtils.isEmpty(start_time)&&StringUtils.isEmpty(end_time)&&StringUtils.isEmpty(interval))
		{
			map.put("interval", 7);
		}
		if(page!=null&&size!=null)
		{
			Integer start=size*(page-1);
			String limitSQL="limit "+start+","+size;
			map.put("limitSQL",limitSQL);
		}
		List<Map<String,Object>> list=statisticsManagerService.searchStatistics(map);
		List<Map<String,Object>> o_list=statisticsManagerService.searchOperateCount(map);
		List<Map<String,Object>> a_list=statisticsManagerService.searchDisMemberCount(map);
		List<Map<String,Object>> result_list=new ArrayList<Map<String,Object>>();
		for(int i=0;i<list.size();i++)
		{
		    Map<String,Object> m=list.get(i);
		    m.putAll(o_list.get(i));
		    m.putAll(a_list.get(i));
		    result_list.add(m);
		}
		Map<String,Object> result_map=new HashMap<String, Object>();
		result_map.put("total_count", total_count);
		result_map.put("data", result_list);
		responseData.setData(result_map);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	
	
	/**
	 * 运营活动图形报表
	 * @return
	 */
	@RequestMapping("/searchOperateImage")
	@ResponseBody
	public ResponseData searchOperateImage()
	{
		List<Map<String,Object>> image_List= statisticsManagerService.queryImageCount();
	    List<Goods> list=statisticsManagerService.selectGoodsByCodePrefix();
	    List<Map<String,Object>> result_list2=new ArrayList<Map<String,Object>>();
	    for(Goods goods:list)
	    {
	    	String child_codes[]= goods.getSeries().split(" ");
	    	for(int i=0;i<child_codes.length;i++)
	    	{
	    		for(Map<String,Object> m:image_List)
		   	    {
		    		if(m.get("image_name").equals(child_codes[i]))
		    		{
		    			 m.put("image_name", goods.getGoodsName()+"("+child_codes[i]+")");
		    			 result_list2.add(m);
		    			 break;
		    		}
		   	       	
		   	    }
	    	}
	    
	    }
		ResponseData responseData=new ResponseData();
		responseData.setData(result_list2);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	
	/**
	 * 运营活动统计
	 * @param start_time 开始日期
	 * @param end_time 结束日期
	 * @param interval 天数
	 * @return
	 */
	@RequestMapping("/searchOperateInfo")
	@ResponseBody
	public ResponseData  searchOperateInfo(String start_time,String end_time,String interval,String type_code,String type_code_child,Integer page,Integer size)
	{
		int total_count=7;
		ResponseData responseData=new ResponseData();
		if(StringUtils.isEmpty(type_code))
		{
			responseData.setData(null);
			responseData.setMsg("类型不能为空！");
			responseData.setStatus("0");
			return responseData;
		}
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time)&&StringUtils.isNotEmpty(interval))
		{
			responseData.setData(null);
			responseData.setMsg("查询天数和日期条件只支持一种模式！");
			responseData.setStatus("0");
			return responseData;
		}
		if((StringUtils.isNotEmpty(start_time)&&StringUtils.isEmpty(end_time))||(StringUtils.isEmpty(start_time)&&StringUtils.isNotEmpty(end_time)))
		{
			responseData.setData(null);
			responseData.setMsg("查询日期条件错误！");
			responseData.setStatus("0");
			return responseData;
		}
		
		Map<String,Object> map=new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(start_time)&&StringUtils.isNotEmpty(end_time))
		{
			SimpleDateFormat formate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date startDay = formate.parse(start_time+" 00:00:00");
				Date endDay = formate.parse(end_time+" 00:00:00");
				Date now=formate.parse(DateUtils.getNow("yyyy-MM-dd HH:mm:ss"));
				int t_flag = DateUtils.getDateDaysBetween(endDay, now);
				if(t_flag<0)
				{
					responseData.setMsg("结束时间不能大于当前时间");
					responseData.setStatus("0");
					return responseData;
				}
				int days = DateUtils.getDateDaysBetween(startDay, endDay);
				if (days < 0) {
					responseData.setMsg("开始时间不能大于结束时间");
					responseData.setStatus("0");
					return responseData;
				}
				if (days > 90) {
					responseData.setMsg("查询订单的时间跨度不能大于三个月");
					responseData.setStatus("0");
					return responseData;
				}
				total_count=days+1;
			} catch (ParseException e) {
				e.printStackTrace();
			}
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		if(StringUtils.isNotEmpty(interval))
		{
			if(Integer.parseInt(interval)>90)
			{
				responseData.setMsg("查询订单的时间跨度不能大于三个月");
				responseData.setStatus("0");
				return responseData;
			}
			map.put("interval", interval);
		}
		if(StringUtils.isEmpty(start_time)&&StringUtils.isEmpty(end_time)&&StringUtils.isEmpty(interval))
		{
			map.put("interval", 7);
		}
		if(page!=null&&size!=null)
		{
			Integer start=size*(page-1);
			String limitSQL="limit "+start+","+size;
			map.put("limitSQL",limitSQL);
		}
		map.put("type_code",type_code);
		map.put("type_code_child",type_code_child);
		List<Map<String,Object>> result_list=statisticsManagerService.searchOperateInfo(map);
		Map<String,Object> result_map=new HashMap<String, Object>();
		result_map.put("total_count", total_count);
		result_map.put("data", result_list);
		responseData.setData(result_map);
		responseData.setMsg("操作成功！");
		responseData.setStatus("1");
		return responseData;
	}
	
	
	/**
	 * 导出
	 */
	@RequestMapping(value = "/exportExcel")
	@ResponseBody
	public void exportExcel(String start_time,String end_time,HttpServletResponse response) {
		Map<String,Object> map=new HashMap<String, Object>();
		if(!StringUtils.isEmpty(start_time)&&!StringUtils.isEmpty(end_time))
		{
			start_time=start_time+" 00:00:00";
			end_time=end_time+" 59:59:59";
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		List<Map<String,Object>> list= statisticsManagerService.queryCommunityUserView(map);
		// 声明一个头部信息
		List<String> headers = Arrays.asList("创建时间", "用户帐号", "邀请用户数", "已认证数", "二级邀请用户数", "二级已认证数","已认证总数", "累计奖励HTL数");
		List<String> header_cloums = Arrays.asList("add_time", "account", "num","num3","num2","num4","total_num","score");
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet();
		sheet.setDefaultColumnWidth((short) 24);
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderBottom(BorderStyle.THIN); // 下边框
		style.setBorderLeft(BorderStyle.THIN);// 左边框
		style.setBorderTop(BorderStyle.THIN);// 上边框
		style.setBorderRight(BorderStyle.THIN);// 右边框
		style.setAlignment(HorizontalAlignment.CENTER); // 居中
		style.setFillForegroundColor((short) 13);// 设置背景色
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom(BorderStyle.THIN); // 下边框
		style2.setBorderLeft(BorderStyle.THIN);// 左边框
		style2.setBorderTop(BorderStyle.THIN);// 上边框
		style2.setBorderRight(BorderStyle.THIN);// 右边框
		style2.setAlignment(HorizontalAlignment.CENTER); // 居中
		HSSFRow row = sheet.createRow(0);

		for (String head : headers) {
			HSSFCell cell = row.createCell(headers.indexOf(head));
			HSSFRichTextString richString = new HSSFRichTextString(head);
			HSSFFont font3 = workbook.createFont();
			font3.setBold(true);
			richString.applyFont(font3);
			cell.setCellValue(richString);
			cell.setCellStyle(style);

		}
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(i + 1);
			for(int j=0;j<header_cloums.size();j++)
			{
				HSSFCell cell = row.createCell(j);
				 String   textValue = list.get(i).get(header_cloums.get(j))+"";
				HSSFRichTextString richString = new HSSFRichTextString(textValue);  
				cell.setCellValue(richString);
				cell.setCellStyle(style2);
			}
		}

		response.setContentType("application/octet-stream");
		try{
		response.setHeader("Content-disposition", "attachment;filename="
				+ new String(("社区管理员统计记录.xls").getBytes("UTF-8"), "ISO8859-1"));// 默认Excel名称
		response.flushBuffer();
		workbook.write(response.getOutputStream());
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

	
	@RequestMapping("/queryCommunityUserView")
	@ResponseBody
	public Object queryCommunityUserView(String account,String start_time,String end_time,HttpServletRequest request)
	{
		String sort = request.getParameter("sort");
		String order = request.getParameter("order");
		Map<String,Object> map=new HashMap<String, Object>();
	   if(!StringUtils.isEmpty(sort))
	    {
	        map.put("orderFiled", "order by "+sort+" "+order);
	    }
		if(!StringUtils.isEmpty(account))
		{
			map.put("account", account);
		}
		if(!StringUtils.isEmpty(start_time)&&!StringUtils.isEmpty(end_time))
		{
			start_time=start_time+" 00:00:00";
			end_time=end_time+" 59:59:59";
			map.put("start_time", start_time);
			map.put("end_time", end_time);
		}
		return statisticsManagerService.queryCommunityUserView(map);
	}
}
